# Copyright (C) 2026. Logiciel élaboré par l'État, via le DEPS.
# Nom des auteures : Flore Cornuet et Camille Parent, DEPS.
# Ce programme informatique a été développé par le DEPS.
# Il permet de produire les résultats du Culture Études 1-2026.

# Ce programme a été exécuté en septembre 2026 avec la version 4.5.3 de R.
# La publication peut être consultée sur le site du Deps. 
# Ce programme utilise les données du pass Culture et du recensement de la population 2022.

# Bien qu'il n'existe aucune obligation légale à ce sujet, les utilisateurs de ce programme sont
# invités à signaler au DEPS leurs travaux issus de la réutilisation de ce code, ainsi que les
# éventuels problèmes ou anomalies qu'ils y rencontreraient, en écrivant à contact.deps@culture.gouv.fr .
# Ce logiciel est régi par la licence EUPL V1.2 soumise au droit français et européen, respectant
# les principes de diffusion des logiciels libres. Vous pouvez utiliser, modifier et/ou redistribuer
# ce programme sous les conditions de la licence EUPL V1.2, telle que diffusée sur
# https://eupl.eu/1.2/fr/ .
# En contrepartie de l'accessibilité au code source et des droits de copie, de modification et de
# redistribution accordés par cette licence, il n'est offert aux utilisateurs qu'une garantie
# limitée. Pour les mêmes raisons, seule une responsabilité restreinte pèse sur les auteures du
# programme, le titulaire des droits patrimoniaux et les concédants successifs.
# À cet égard l'attention de l'utilisateur est attirée sur les risques associés au chargement, à
# l'utilisation, à la modification et/ou au développement et à la reproduction du logiciel par
# l'utilisateur étant donné sa spécificité de logiciel libre, qui peut le rendre complexe à
# manipuler et qui le réserve donc à des développeurs et des professionnels avertis possédant des
# connaissances informatiques approfondies. Les utilisateurs sont donc invités à charger et tester
# l'adéquation du logiciel à leurs besoins dans des conditions permettant d'assurer la sécurité de
# leurs systèmes et ou de leurs données et, plus généralement, à l'utiliser et l'exploiter dans les
# mêmes conditions de sécurité.

# chargement des packages ####

library(data.table); library(glue); library(ggplot2)
library(duckdb)
library(tidyverse)
library(readxl)

# création de la connexion duckdb ####

con <- dbConnect(duckdb())
dbExecute(con, "SET threads = 4")
dbExecute(con, "SET preserve_insertion_order = false")
dbExecute(con, "SET memory_limit = '16GB'")


# correspondance entre codegeo et libdens7 ####

tab_densite <- read_xlsx(
  chemin_densite,
  skip = 4
)
duckdb_register(con, "tab_dens", tab_densite)

dbExecute(con, glue("
  CREATE OR REPLACE TABLE bookings_filtres AS
  SELECT * FROM read_parquet('{chemin_intermediaires}bookings_filtres.parquet')
"))

cat_dens <- dbGetQuery(con, glue("
  WITH addr_uniq AS (
    SELECT ad.user_id, ANY_VALUE(d.LIBDENS7) AS LIBDENS7
    FROM (
      SELECT ua.user_id,
             CASE
               WHEN gi.city_code BETWEEN '75101' AND '75120' THEN '75056'
               WHEN gi.city_code BETWEEN '13201' AND '13216' THEN '13055'
               WHEN gi.city_code BETWEEN '69381' AND '69389' THEN '69123'
               ELSE gi.city_code
             END AS code_commune
      FROM read_parquet('{chemin_addr}') ua
      LEFT JOIN read_parquet('{chemin_iris}') gi
        ON ua.user_iris_internal_id = gi.iris_internal_id
    ) ad
    LEFT JOIN tab_dens d ON ad.code_commune = d.CODGEO
    GROUP BY ad.user_id
  ),
  dep AS (
    SELECT deposit_id, user_id
    FROM read_parquet('{chemin_deposit}')
    WHERE deposit_amount = 300
      AND deposit_creation_date >= '2021-05-21'
      AND deposit_creation_date <  '2025-02-28'
      AND deposit_expiration_date < '2025-02-28'
  ),
  book AS (
    SELECT bf.user_id,
           bf.booking_intermediary_amount AS montant,
           CASE
             WHEN bf.offer_category = 'LIVRE'               THEN 'Livre'
             WHEN bf.offer_category = 'CINEMA'              THEN 'Cinéma'
             WHEN bf.offer_category = 'MUSIQUE_LIVE'        THEN 'Concerts et festivals'
             WHEN bf.offer_category = 'INSTRUMENT'          THEN 'Instrument de musique'
             WHEN bf.offer_category = 'MUSIQUE_ENREGISTREE' THEN 'Musique enregistrée'
             WHEN bf.offer_category = 'JEU'                 THEN 'Jeu'
             WHEN bf.offer_category = 'BEAUX_ARTS'          THEN 'Beaux-arts'
             WHEN bf.offer_category = 'SPECTACLE'           THEN 'Spectacle vivant'
             WHEN bf.offer_category = 'FILM'                THEN 'Film'
             ELSE 'Autre'
           END AS cat_group
    FROM bookings_filtres bf
    JOIN dep d ON bf.deposit_id = d.deposit_id
    JOIN read_parquet('{chemin_user}') u ON bf.user_id = u.user_id
    WHERE EXTRACT(YEAR FROM u.user_birth_date) BETWEEN 2003 AND 2005
  )
  SELECT a.LIBDENS7, book.cat_group, SUM(book.montant) AS depense
  FROM book
  LEFT JOIN addr_uniq a ON book.user_id = a.user_id
  GROUP BY a.LIBDENS7, book.cat_group
"))
setDT(cat_dens)
cat_dens[, part := depense / sum(depense), by = LIBDENS7]

ordre_dens7 <- c(
  "Grands centres urbains","Centres urbains intermédiaires","Ceintures urbaines",
  "Petites villes","Bourgs ruraux","Rural à habitat dispersé","Rural à habitat très dispersé"
)
ordre_cat <- c("Livre","Cinéma","Concerts et festivals","Instrument de musique",
               "Musique enregistrée","Jeu","Beaux-arts","Spectacle vivant","Film","Autre")

cat_dens[!is.na(LIBDENS7)] |>
  ggplot(aes(x = factor(cat_group, levels = ordre_cat),
             y = part,
             fill = factor(LIBDENS7, levels = ordre_dens7))) +
  geom_col(position = position_dodge(width = 0.85), width = 0.8) +
  scale_y_continuous(labels = scales::percent) +
  scale_fill_viridis_d(option = "D", name = "Catégorie de densité") +
  labs(
    title = "Répartition des dépenses par catégorie selon la densité de la zone d'habitation",
    subtitle = "Bénéficiaires nés en 2003-2005, chèque de 300€ (champ création + expiration)",
    x = "Catégorie de dépense",
    y = "Part des dépenses"
  ) +
  theme_minimal(base_size = 13) +
  theme(axis.text.x = element_text(angle = 45, hjust = 1),
        legend.position = "bottom")


# exportation chiffres excel ####

head(cat_dens[!is.na(LIBDENS7)])

cat_dens[!is.na(LIBDENS7)] %>% 
  select(-depense) %>% 
  spread(LIBDENS7,part)

write.table(cat_dens[!is.na(LIBDENS7)] %>% 
              select(-depense) %>% 
              spread(LIBDENS7,part), "clipboard-256", sep = "\t", row.names = FALSE,dec=",")
